package com.quanquan.store.repository;

import com.quanquan.store.entity.CouponCode;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Modifying;
import org.springframework.data.jpa.repository.Query;
import org.springframework.transaction.annotation.Transactional;

import java.util.List;

/**
 * @ClassName:CouponCodeRepository
 * @Description:TODO
 * @Author:xiongjunbao
 * @Date:2019/3/18 14:43
 * @Version 1.0
 */
public interface CouponCodeRepository extends JpaRepository<CouponCode, String>, JpaSpecificationExecutor<CouponCode> {

    /**
     * 券购买时将券锁定
     *
     * @param codeId
     * @param userId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update oo_coupon_code set user_id = ?2,add_time = ?3,use_time_start = ?6, sender_name = ?4, use_time_end = ?5 where id=?1 and status = -1", nativeQuery = true)
    public int  addUserCouponCodeAndStatus1(String codeId, String userId, String addTime, String senderName, String useEnd, String useTimeStart);

    /**
     * 券购买成功时将券锁定
     *
     * @param codeId
     * @param userId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update oo_coupon_code set user_id = ?2,status = 0,add_time = ?3,use_time_start = ?6, sender_name = ?4, use_time_end = ?5 where id=?1 and status = -1", nativeQuery = true)
    public int addUserCouponCode1(String codeId, String userId, String addTime, String senderName, String useEnd, String useTimeStart);

    /**
     * 商管发券
     *
     * @param codeId
     * @param userId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update oo_coupon_code set user_id = ?2,status = -1,add_time = ?3,use_time_start = ?6, sender_name = ?4, use_time_end = ?5 where id=?1 and status = -1", nativeQuery = true)
    public int addUserCouponCode2(String codeId, String userId, String addTime, String senderName, String useEnd, String useTimeStart);


    @Query(value = "SELECT * FROM oo_coupon_code WHERE status = -1 and coupon_id = ?1 ORDER BY create_time DESC", nativeQuery = true)
    List<CouponCode> queryByCouponIdAndStatus(String couponId);


    @Query(value = "SELECT * FROM oo_coupon_code WHERE status = ?2 AND user_id = ?1 AND type = ?5 AND use_time_end >=now() ORDER BY add_time DESC LIMIT ?3,?4", nativeQuery = true)
    List<CouponCode> queryCodeListByUserId(String userId, Integer status, Integer count, Integer size, Integer type);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE user_id = ?1 AND use_time_end < now() AND status >= 0 ORDER BY add_time DESC LIMIT ?2,?3", nativeQuery = true)
    List<CouponCode> queryOverdue(String userId, Integer count, Integer size);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE id = ?1", nativeQuery = true)
    CouponCode queryById(String id);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE coupon_code = ?1 and status = 0", nativeQuery = true)
    CouponCode queryByCouponCodeAndUserId(String couponCode);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE coupon_code = ?1", nativeQuery = true)
    CouponCode queryByCouponCodeAndUserId1(String couponCode);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE status = ?1 ORDER BY create_time DESC limit ?2,?3", nativeQuery = true)
    List<CouponCode> queryByStatus(Integer status, Integer count, Integer size, Integer type);


    @Query(value = "SELECT * FROM oo_coupon_code WHERE coupon_code = ?1", nativeQuery = true)
    CouponCode queryByCouponCode(String couponCode);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE use_admin_id = ?1 and status = 1 order by use_time desc  limit ?2,?3", nativeQuery = true)
    List<CouponCode> queryByUseAdminId(String useAdminId, Integer count, Integer size, Integer type);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE user_id = ?1 and LOCATE(?2,add_time)>0 and status >=0", nativeQuery = true)
    List<CouponCode> queryByUserId(String userId, String today);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE user_id = ?1 and coupon_id = ?2 and LOCATE(?3,add_time)>0 and status >=0", nativeQuery = true)
    List<CouponCode> queryByUserIdAndCouponId(String userId, String couponId, String today);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE user_id = ?1 and coupon_id = ?2 and status >=0", nativeQuery = true)
    List<CouponCode> queryByUserIdAndCouponId(String userId, String couponId);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE coupon_id = ?1 and status = -1", nativeQuery = true)
    List<CouponCode> queryByCouponIdAndUseEnd(String couponId);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE status = 1 and car_number is not null limit ?1,?2", nativeQuery = true)
    List<CouponCode> parkingRecord(Integer count, Integer size);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE STATUS >=0 and type = ?1", nativeQuery = true)
    List<CouponCode> findGetCoupon(Integer type);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE STATUS = 1 and type = ?1", nativeQuery = true)
    List<CouponCode> findUseCoupon(Integer type);


    @Query(value = "SELECT * FROM oo_coupon_code WHERE status = -1 and coupon_id = ?1 and user_id = ?2 ORDER BY create_time DESC", nativeQuery = true)
    List<CouponCode> queryByCouponIdAndUserIdAndStatus(String couponId, String userId);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE status = -1 and coupon_id = ?1 and store_id = ?2 ORDER BY create_time DESC", nativeQuery = true)
    List<CouponCode> queryByCouponIdAndStoreId(String couponId, String storeId);


    @Query(value = "SELECT * FROM oo_coupon_code WHERE STATUS = 1 and coupon_id = ?1", nativeQuery = true)
    List<CouponCode> findByCouponId(String couponId);

    @Query(value = "SELECT * FROM oo_coupon_code WHERE STATUS >= 0 and coupon_id = ?1", nativeQuery = true)
    List<CouponCode> findByCouponIdAndStatus(String couponId);


    /**
     * 通过用户id，商城id查找当天用户购买该券的数量
     * @param userId
     * @param shopMallId
     * @param today
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon_code WHERE user_id = ?1 and shop_mall_id = ?2 and LOCATE(?3,add_time)>0 and status >=0", nativeQuery = true)
    List<CouponCode> queryByUserIdAndShopMallId(String userId, String shopMallId, String today);


    /**
     * 通过用户id，商城id查找用户购买期内购买该券的数量
     * @param userId
     * @param couponId
     * @param buyTimeStart
     * @param buyTimeEnd
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon_code WHERE user_id = ?1 and shop_mall_id = ?2 and status >=0 and add_time between ?3 and ?4 ", nativeQuery = true)
    List<CouponCode> queryByUserIdAndCouponIdAndBetween(String userId, String couponId, String buyTimeStart, String buyTimeEnd);


    /**
     * 券购买时将券锁定
     *
     * @param codeId
     * @param userId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update oo_coupon_code set user_id = ?2,add_time = ?3,use_time_start = ?3, sender_name = ?4, use_time_end = ?5,shop_mall_id = ?6 where id=?1 and status = -1", nativeQuery = true)
    public int addUserCouponCodeAndStatus(String codeId, String userId, String addTime, String senderName, String useEnd, String shopMallId);

    /**
     * 券购买成功时将券锁定
     *
     * @param codeId
     * @param userId
     * @return
     */
    @Transactional
    @Modifying
    @Query(value = "update oo_coupon_code set user_id = ?2,status = 0,add_time = ?3,use_time_start = ?7, sender_name = ?4, use_time_end = ?5 ,order_num = ?6 where id=?1 and status = -1", nativeQuery = true)
    public int addUserCouponCode(String codeId, String userId, String addTime, String senderName, String useEnd, String orderNum, String useTimeStart);

    /**
     *
     * @param couponId
     * @param userId
     * @param count
     * @return
     */
    @Query(value = "SELECT * FROM oo_coupon_code WHERE coupon_id = ?1 AND user_id = ?2  AND STATUS = -1 LIMIT ?3",nativeQuery = true)
    List<CouponCode> findByCouponIdAndUserIdByCount(String couponId, String userId, Integer count);


}
